{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# GNBR Parsing Script\n",
    "This notebook gives an in depth explanation of the code used to parse GNBR from its native format into node and edge file for import into Neo4j using the bulk importer tool. In the actual build module the code will be put into functions for purposes of style and hygeine.  Use this as a reference or protoyping tool when playing around with changes to the GNBR neo4j database.\n",
    "\n",
    "Planned improvements are to implement this using the dask library, as the script as it currently stands is quite emory intensive.  It came close to crashing my personal machine, which is top spec in terms of RAM."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import gzip\n",
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Global Variables\n",
    "Here we set declare \n",
    "1. The file header for the GNBR dependency path files (i.e. part 2 files\n",
    "2. Source directory the raw GNBR csv files reside (i.e. download directory)\n",
    "3. Destination and filenames for the parsed GNBR node and edge files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "HEADER = [\n",
    "    \"pmid\", \"loc\", \n",
    "    \"subj_name\", \"subj_loc\", \n",
    "    \"obj_name\", \"obj_loc\",\n",
    "    \"subj_name_raw\", \"obj_name_raw\", \n",
    "    \"subj_id\", \"obj_id\", \n",
    "    \"subj_type\", \"obj_type\", \n",
    "    \"path\", \"text\"\n",
    "]\n",
    "\n",
    "DWNLD_DIR=os.path.expanduser(\"~/test/gnbr\")\n",
    "NEO_DIR = os.path.expanduser(\"~/neo4j/import\")\n",
    "ENTITIES = 'entity.csv.gz'\n",
    "MENTIONS = 'mention.csv.gz'\n",
    "SENTENCES = 'sentence.csv.gz'\n",
    "THEMES = 'theme.csv.gz'\n",
    "STATEMENTS = 'statement.csv.gz'\n",
    "HAS_MENTION = 'has_mention.csv.gz'\n",
    "IN_SENTENCE = 'in_sentence.csv.gz'\n",
    "HAS_THEME = 'has_theme.csv.gz'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Import into Pandas\n",
    "Now we get the filenames and import the GNBR files into a Pandas Dataframe.   I orginially did everything streaming because I was worried about speed and memory, but it turns out pandas can handle all of GNBR at once.  The ease of data manipulation more than makes up for an loss in performance."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "source = DWNLD_DIR\n",
    "theme_files = sorted( [os.path.join(source, file) for file in os.listdir(source) if '-i-' in file] )\n",
    "path_files = sorted( [os.path.join(source, file) for file in os.listdir(source) if '-ii-' in file] )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this block of code we join theme (part 1) and path (part 2) files using dependency paths as the key, and then append each joined Dataframe to the results of the previous iteration.  When we are done, we end up with all the data in one massive data frame.  BIGGGG DATA!!!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "gnbr_df = pd.DataFrame()\n",
    "for theme_file, path_file in zip(theme_files, path_files):\n",
    "    theme_df = pd.read_csv(theme_file, compression='gzip', header=0, sep='\\t')\n",
    "    path_df = pd.read_csv(path_file, compression='gzip', header=None, names=HEADER, sep='\\t')\n",
    "    path_df = path_df.dropna(0).drop_duplicates()\n",
    "    path_df.path = path_df.path.str.lower()\n",
    "    merged_df = path_df.merge(theme_df, how=\"inner\", on=['path'])\n",
    "    gnbr_df = pd.concat([gnbr_df, merged_df], join='outer', ignore_index=True, sort=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>pmid</th>\n",
       "      <th>loc</th>\n",
       "      <th>subj_name</th>\n",
       "      <th>subj_loc</th>\n",
       "      <th>obj_name</th>\n",
       "      <th>obj_loc</th>\n",
       "      <th>subj_name_raw</th>\n",
       "      <th>obj_name_raw</th>\n",
       "      <th>subj_id</th>\n",
       "      <th>obj_id</th>\n",
       "      <th>...</th>\n",
       "      <th>V+</th>\n",
       "      <th>V+.ind</th>\n",
       "      <th>I</th>\n",
       "      <th>I.ind</th>\n",
       "      <th>H</th>\n",
       "      <th>H.ind</th>\n",
       "      <th>Rg</th>\n",
       "      <th>Rg.ind</th>\n",
       "      <th>Q</th>\n",
       "      <th>Q.ind</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>18509642</td>\n",
       "      <td>11</td>\n",
       "      <td>10058-F4</td>\n",
       "      <td>1516,1524</td>\n",
       "      <td>tumor</td>\n",
       "      <td>1492,1497</td>\n",
       "      <td>10058-F4</td>\n",
       "      <td>tumor</td>\n",
       "      <td>MESH:C524814</td>\n",
       "      <td>MESH:D009369</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20801893</td>\n",
       "      <td>10</td>\n",
       "      <td>10074-G5</td>\n",
       "      <td>1713,1721</td>\n",
       "      <td>tumor</td>\n",
       "      <td>1689,1694</td>\n",
       "      <td>10074-G5</td>\n",
       "      <td>tumor</td>\n",
       "      <td>MESH:C534883</td>\n",
       "      <td>MESH:D009369</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8027220</td>\n",
       "      <td>3</td>\n",
       "      <td>17-deoxysteroids</td>\n",
       "      <td>517,533</td>\n",
       "      <td>hypertension</td>\n",
       "      <td>463,475</td>\n",
       "      <td>17-deoxysteroids</td>\n",
       "      <td>hypertension</td>\n",
       "      <td>MESH:C006022</td>\n",
       "      <td>MESH:D006973</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>18936997</td>\n",
       "      <td>15</td>\n",
       "      <td>2,3,7,8-tetrachlorodibenzo-p-dioxin</td>\n",
       "      <td>2376,2411</td>\n",
       "      <td>toxicity</td>\n",
       "      <td>2337,2345</td>\n",
       "      <td>2,3,7,8-tetrachlorodibenzo-p-dioxin</td>\n",
       "      <td>toxicity</td>\n",
       "      <td>MESH:D013749</td>\n",
       "      <td>MESH:D064420</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>26279999</td>\n",
       "      <td>0</td>\n",
       "      <td>3,5-Diiodothyronine</td>\n",
       "      <td>85,104</td>\n",
       "      <td>Cardiac_Illness</td>\n",
       "      <td>33,48</td>\n",
       "      <td>3,5-Diiodothyronine</td>\n",
       "      <td>Cardiac Illness</td>\n",
       "      <td>MESH:C030102</td>\n",
       "      <td>MESH:D006331</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 78 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       pmid  loc                            subj_name   subj_loc  \\\n",
       "0  18509642   11                             10058-F4  1516,1524   \n",
       "1  20801893   10                             10074-G5  1713,1721   \n",
       "2   8027220    3                     17-deoxysteroids    517,533   \n",
       "3  18936997   15  2,3,7,8-tetrachlorodibenzo-p-dioxin  2376,2411   \n",
       "4  26279999    0                  3,5-Diiodothyronine     85,104   \n",
       "\n",
       "          obj_name    obj_loc                        subj_name_raw  \\\n",
       "0            tumor  1492,1497                             10058-F4   \n",
       "1            tumor  1689,1694                             10074-G5   \n",
       "2     hypertension    463,475                     17-deoxysteroids   \n",
       "3         toxicity  2337,2345  2,3,7,8-tetrachlorodibenzo-p-dioxin   \n",
       "4  Cardiac_Illness      33,48                  3,5-Diiodothyronine   \n",
       "\n",
       "      obj_name_raw       subj_id        obj_id  ...    V+ V+.ind   I I.ind  \\\n",
       "0            tumor  MESH:C524814  MESH:D009369  ...   NaN    NaN NaN   NaN   \n",
       "1            tumor  MESH:C534883  MESH:D009369  ...   NaN    NaN NaN   NaN   \n",
       "2     hypertension  MESH:C006022  MESH:D006973  ...   NaN    NaN NaN   NaN   \n",
       "3         toxicity  MESH:D013749  MESH:D064420  ...   NaN    NaN NaN   NaN   \n",
       "4  Cardiac Illness  MESH:C030102  MESH:D006331  ...   NaN    NaN NaN   NaN   \n",
       "\n",
       "    H  H.ind  Rg  Rg.ind   Q  Q.ind  \n",
       "0 NaN    NaN NaN     NaN NaN    NaN  \n",
       "1 NaN    NaN NaN     NaN NaN    NaN  \n",
       "2 NaN    NaN NaN     NaN NaN    NaN  \n",
       "3 NaN    NaN NaN     NaN NaN    NaN  \n",
       "4 NaN    NaN NaN     NaN NaN    NaN  \n",
       "\n",
       "[5 rows x 78 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gnbr_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Clean Up Data\n",
    "Here we do some basic hoursekeeping to clean up the data and produce some unique identifiers that will make our life easier in the future.  \n",
    "\n",
    "##### Generate Sentence IDs\n",
    "In this next block of code, we hash the text of each sentence to produce a unique identifier.  We do this because it is much easier of Pandas and Neo4j to compare and match hash values than long strings of text."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>pmid</th>\n",
       "      <th>loc</th>\n",
       "      <th>subj_name</th>\n",
       "      <th>subj_loc</th>\n",
       "      <th>obj_name</th>\n",
       "      <th>obj_loc</th>\n",
       "      <th>subj_name_raw</th>\n",
       "      <th>obj_name_raw</th>\n",
       "      <th>subj_id</th>\n",
       "      <th>obj_id</th>\n",
       "      <th>...</th>\n",
       "      <th>V+.ind</th>\n",
       "      <th>I</th>\n",
       "      <th>I.ind</th>\n",
       "      <th>H</th>\n",
       "      <th>H.ind</th>\n",
       "      <th>Rg</th>\n",
       "      <th>Rg.ind</th>\n",
       "      <th>Q</th>\n",
       "      <th>Q.ind</th>\n",
       "      <th>sentence_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>18509642</td>\n",
       "      <td>11</td>\n",
       "      <td>10058-F4</td>\n",
       "      <td>1516,1524</td>\n",
       "      <td>tumor</td>\n",
       "      <td>1492,1497</td>\n",
       "      <td>10058-F4</td>\n",
       "      <td>tumor</td>\n",
       "      <td>MESH:C524814</td>\n",
       "      <td>MESH:D009369</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>f407c175ed3c5bed6e49e853bd1521db</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20801893</td>\n",
       "      <td>10</td>\n",
       "      <td>10074-G5</td>\n",
       "      <td>1713,1721</td>\n",
       "      <td>tumor</td>\n",
       "      <td>1689,1694</td>\n",
       "      <td>10074-G5</td>\n",
       "      <td>tumor</td>\n",
       "      <td>MESH:C534883</td>\n",
       "      <td>MESH:D009369</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>432e5c439550f059249b8e05273cd1b4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8027220</td>\n",
       "      <td>3</td>\n",
       "      <td>17-deoxysteroids</td>\n",
       "      <td>517,533</td>\n",
       "      <td>hypertension</td>\n",
       "      <td>463,475</td>\n",
       "      <td>17-deoxysteroids</td>\n",
       "      <td>hypertension</td>\n",
       "      <td>MESH:C006022</td>\n",
       "      <td>MESH:D006973</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>b7e17c4dd71970206b4bfc31b82d4eca</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>18936997</td>\n",
       "      <td>15</td>\n",
       "      <td>2,3,7,8-tetrachlorodibenzo-p-dioxin</td>\n",
       "      <td>2376,2411</td>\n",
       "      <td>toxicity</td>\n",
       "      <td>2337,2345</td>\n",
       "      <td>2,3,7,8-tetrachlorodibenzo-p-dioxin</td>\n",
       "      <td>toxicity</td>\n",
       "      <td>MESH:D013749</td>\n",
       "      <td>MESH:D064420</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>71865f16aa1424cb09cac2cf609ea7cb</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>26279999</td>\n",
       "      <td>0</td>\n",
       "      <td>3,5-Diiodothyronine</td>\n",
       "      <td>85,104</td>\n",
       "      <td>Cardiac_Illness</td>\n",
       "      <td>33,48</td>\n",
       "      <td>3,5-Diiodothyronine</td>\n",
       "      <td>Cardiac Illness</td>\n",
       "      <td>MESH:C030102</td>\n",
       "      <td>MESH:D006331</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>08731188178997f788b85b96e98b9120</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 79 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       pmid  loc                            subj_name   subj_loc  \\\n",
       "0  18509642   11                             10058-F4  1516,1524   \n",
       "1  20801893   10                             10074-G5  1713,1721   \n",
       "2   8027220    3                     17-deoxysteroids    517,533   \n",
       "3  18936997   15  2,3,7,8-tetrachlorodibenzo-p-dioxin  2376,2411   \n",
       "4  26279999    0                  3,5-Diiodothyronine     85,104   \n",
       "\n",
       "          obj_name    obj_loc                        subj_name_raw  \\\n",
       "0            tumor  1492,1497                             10058-F4   \n",
       "1            tumor  1689,1694                             10074-G5   \n",
       "2     hypertension    463,475                     17-deoxysteroids   \n",
       "3         toxicity  2337,2345  2,3,7,8-tetrachlorodibenzo-p-dioxin   \n",
       "4  Cardiac_Illness      33,48                  3,5-Diiodothyronine   \n",
       "\n",
       "      obj_name_raw       subj_id        obj_id  \\\n",
       "0            tumor  MESH:C524814  MESH:D009369   \n",
       "1            tumor  MESH:C534883  MESH:D009369   \n",
       "2     hypertension  MESH:C006022  MESH:D006973   \n",
       "3         toxicity  MESH:D013749  MESH:D064420   \n",
       "4  Cardiac Illness  MESH:C030102  MESH:D006331   \n",
       "\n",
       "                 ...                V+.ind   I I.ind   H  H.ind  Rg  Rg.ind  \\\n",
       "0                ...                   NaN NaN   NaN NaN    NaN NaN     NaN   \n",
       "1                ...                   NaN NaN   NaN NaN    NaN NaN     NaN   \n",
       "2                ...                   NaN NaN   NaN NaN    NaN NaN     NaN   \n",
       "3                ...                   NaN NaN   NaN NaN    NaN NaN     NaN   \n",
       "4                ...                   NaN NaN   NaN NaN    NaN NaN     NaN   \n",
       "\n",
       "    Q  Q.ind                       sentence_id  \n",
       "0 NaN    NaN  f407c175ed3c5bed6e49e853bd1521db  \n",
       "1 NaN    NaN  432e5c439550f059249b8e05273cd1b4  \n",
       "2 NaN    NaN  b7e17c4dd71970206b4bfc31b82d4eca  \n",
       "3 NaN    NaN  71865f16aa1424cb09cac2cf609ea7cb  \n",
       "4 NaN    NaN  08731188178997f788b85b96e98b9120  \n",
       "\n",
       "[5 rows x 79 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import hashlib\n",
    "sentence_ids = gnbr_df['text'].copy()\n",
    "sentence_ids = sentence_ids.astype(str).str.encode('utf-8')\n",
    "sentence_ids = sentence_ids.apply(lambda x: hashlib.md5(x).hexdigest())\n",
    "gnbr_df.loc[:,'sentence_id'] = sentence_ids\n",
    "gnbr_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Generate Dependency Path IDs\n",
    "Here we do something similar to sentence ids (hashing), with one key difference.  We hash triples with subject type, object type, and dependency path.  We include the subject and object types because the same dependency path can map to different types of distributions (i.e. theme sets) depending on what types of entities are involved.  This makes paths non-unique identifiers for theme distributions, which crashes the neo4j import.  Adding subject and object types creates a unique triple tht we can use as the id."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>pmid</th>\n",
       "      <th>loc</th>\n",
       "      <th>subj_name</th>\n",
       "      <th>subj_loc</th>\n",
       "      <th>obj_name</th>\n",
       "      <th>obj_loc</th>\n",
       "      <th>subj_name_raw</th>\n",
       "      <th>obj_name_raw</th>\n",
       "      <th>subj_id</th>\n",
       "      <th>obj_id</th>\n",
       "      <th>...</th>\n",
       "      <th>I</th>\n",
       "      <th>I.ind</th>\n",
       "      <th>H</th>\n",
       "      <th>H.ind</th>\n",
       "      <th>Rg</th>\n",
       "      <th>Rg.ind</th>\n",
       "      <th>Q</th>\n",
       "      <th>Q.ind</th>\n",
       "      <th>sentence_id</th>\n",
       "      <th>path_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>18509642</td>\n",
       "      <td>11</td>\n",
       "      <td>10058-F4</td>\n",
       "      <td>1516,1524</td>\n",
       "      <td>tumor</td>\n",
       "      <td>1492,1497</td>\n",
       "      <td>10058-F4</td>\n",
       "      <td>tumor</td>\n",
       "      <td>MESH:C524814</td>\n",
       "      <td>MESH:D009369</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>f407c175ed3c5bed6e49e853bd1521db</td>\n",
       "      <td>05d11ea4b1d924abafac60a269c3347c</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20801893</td>\n",
       "      <td>10</td>\n",
       "      <td>10074-G5</td>\n",
       "      <td>1713,1721</td>\n",
       "      <td>tumor</td>\n",
       "      <td>1689,1694</td>\n",
       "      <td>10074-G5</td>\n",
       "      <td>tumor</td>\n",
       "      <td>MESH:C534883</td>\n",
       "      <td>MESH:D009369</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>432e5c439550f059249b8e05273cd1b4</td>\n",
       "      <td>05d11ea4b1d924abafac60a269c3347c</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8027220</td>\n",
       "      <td>3</td>\n",
       "      <td>17-deoxysteroids</td>\n",
       "      <td>517,533</td>\n",
       "      <td>hypertension</td>\n",
       "      <td>463,475</td>\n",
       "      <td>17-deoxysteroids</td>\n",
       "      <td>hypertension</td>\n",
       "      <td>MESH:C006022</td>\n",
       "      <td>MESH:D006973</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>b7e17c4dd71970206b4bfc31b82d4eca</td>\n",
       "      <td>05d11ea4b1d924abafac60a269c3347c</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>18936997</td>\n",
       "      <td>15</td>\n",
       "      <td>2,3,7,8-tetrachlorodibenzo-p-dioxin</td>\n",
       "      <td>2376,2411</td>\n",
       "      <td>toxicity</td>\n",
       "      <td>2337,2345</td>\n",
       "      <td>2,3,7,8-tetrachlorodibenzo-p-dioxin</td>\n",
       "      <td>toxicity</td>\n",
       "      <td>MESH:D013749</td>\n",
       "      <td>MESH:D064420</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>71865f16aa1424cb09cac2cf609ea7cb</td>\n",
       "      <td>05d11ea4b1d924abafac60a269c3347c</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>26279999</td>\n",
       "      <td>0</td>\n",
       "      <td>3,5-Diiodothyronine</td>\n",
       "      <td>85,104</td>\n",
       "      <td>Cardiac_Illness</td>\n",
       "      <td>33,48</td>\n",
       "      <td>3,5-Diiodothyronine</td>\n",
       "      <td>Cardiac Illness</td>\n",
       "      <td>MESH:C030102</td>\n",
       "      <td>MESH:D006331</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>08731188178997f788b85b96e98b9120</td>\n",
       "      <td>05d11ea4b1d924abafac60a269c3347c</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 80 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       pmid  loc                            subj_name   subj_loc  \\\n",
       "0  18509642   11                             10058-F4  1516,1524   \n",
       "1  20801893   10                             10074-G5  1713,1721   \n",
       "2   8027220    3                     17-deoxysteroids    517,533   \n",
       "3  18936997   15  2,3,7,8-tetrachlorodibenzo-p-dioxin  2376,2411   \n",
       "4  26279999    0                  3,5-Diiodothyronine     85,104   \n",
       "\n",
       "          obj_name    obj_loc                        subj_name_raw  \\\n",
       "0            tumor  1492,1497                             10058-F4   \n",
       "1            tumor  1689,1694                             10074-G5   \n",
       "2     hypertension    463,475                     17-deoxysteroids   \n",
       "3         toxicity  2337,2345  2,3,7,8-tetrachlorodibenzo-p-dioxin   \n",
       "4  Cardiac_Illness      33,48                  3,5-Diiodothyronine   \n",
       "\n",
       "      obj_name_raw       subj_id        obj_id  \\\n",
       "0            tumor  MESH:C524814  MESH:D009369   \n",
       "1            tumor  MESH:C534883  MESH:D009369   \n",
       "2     hypertension  MESH:C006022  MESH:D006973   \n",
       "3         toxicity  MESH:D013749  MESH:D064420   \n",
       "4  Cardiac Illness  MESH:C030102  MESH:D006331   \n",
       "\n",
       "                 ...                  I I.ind   H H.ind  Rg  Rg.ind   Q  \\\n",
       "0                ...                NaN   NaN NaN   NaN NaN     NaN NaN   \n",
       "1                ...                NaN   NaN NaN   NaN NaN     NaN NaN   \n",
       "2                ...                NaN   NaN NaN   NaN NaN     NaN NaN   \n",
       "3                ...                NaN   NaN NaN   NaN NaN     NaN NaN   \n",
       "4                ...                NaN   NaN NaN   NaN NaN     NaN NaN   \n",
       "\n",
       "   Q.ind                       sentence_id                           path_id  \n",
       "0    NaN  f407c175ed3c5bed6e49e853bd1521db  05d11ea4b1d924abafac60a269c3347c  \n",
       "1    NaN  432e5c439550f059249b8e05273cd1b4  05d11ea4b1d924abafac60a269c3347c  \n",
       "2    NaN  b7e17c4dd71970206b4bfc31b82d4eca  05d11ea4b1d924abafac60a269c3347c  \n",
       "3    NaN  71865f16aa1424cb09cac2cf609ea7cb  05d11ea4b1d924abafac60a269c3347c  \n",
       "4    NaN  08731188178997f788b85b96e98b9120  05d11ea4b1d924abafac60a269c3347c  \n",
       "\n",
       "[5 rows x 80 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "path_ids = gnbr_df['path'].astype(str)  + gnbr_df['subj_type'].astype(str) + gnbr_df['obj_type'].astype(str)\n",
    "path_ids = path_ids.astype(str).str.encode('utf-8')\n",
    "path_ids = path_ids.apply(lambda x: hashlib.md5(x).hexdigest())\n",
    "gnbr_df.loc[:,'path_id'] = path_ids\n",
    "gnbr_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Fix Chemical, Gene, and Document IDs\n",
    "Here we clean up the chemical and gene identifiers.  Some chemical identifiers are missing their MESH prefixes, and some genes are missing theie entrez prefixes.  Also some genes have a taxon string appended on the end.  The three things going on here are:\n",
    "1. Split off the Taxon string where it is present\n",
    "2. Use regexes to find MESH and NCBIGENE IDs missing prefixes, and fix them\n",
    "3. Add taxonomy prefix and put everything back into the Dataframe\n",
    "\n",
    "We do this for both the subjects and the objects of each sentence.\n",
    "\n",
    "##### Fix Subject IDs\n",
    "First we split the uri field to separate the Texonomy ids from genes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "uri_split = gnbr_df['subj_id'].str.split('(', expand=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Then we parse look for mesh id and entez gene ids without prefixes, prepend them on, and place them back in the subject uri field."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "uris = uri_split[0]\n",
    "uris = uris.str.replace(r'^(C\\d+)$', lambda m: 'MESH:' + m.group(0))\n",
    "uris = uris.str.replace(r'^(D\\d+)$', lambda m: 'MESH:' + m.group(0))\n",
    "uris = uris.str.replace(r'^(\\d+)$', lambda m: 'NCBIGENE:' + m.group(0))\n",
    "gnbr_df['subj_id'] = uris"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, we add the prefix for Taxa, assume that it is human where not indicated (this may be a faulty assumption), and add to the dataframe as a new column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "species = uri_split[1].str.strip(')')\n",
    "species = species.str.replace('Tax', 'Taxonomy')\n",
    "species = species.fillna(value=\"Taxonomy:9606\")\n",
    "species[gnbr_df['subj_type'] != 'Gene'] = ''\n",
    "gnbr_df['subj_species'] = species"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Fix Object IDs\n",
    "Object IDs follow the exact same procedure as the subject IDs.  I thought about writing a function, but I think the explicit indication that you need to do for both subject and object IDs, outweighs any loss in style points from cutting and pasting code."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "uri_split = gnbr_df['obj_id'].str.split('(', expand=True)\n",
    "\n",
    "uris = uri_split[0]\n",
    "uris = uris.str.replace(r'^(C\\d+)$', lambda m: 'MESH:' + m.group(0))\n",
    "uris = uris.str.replace(r'^(D\\d+)$', lambda m: 'MESH:' + m.group(0))\n",
    "uris = uris.str.replace(r'^(\\d+)$', lambda m: 'NCBIGENE:' + m.group(0))\n",
    "gnbr_df['obj_id'] = uris\n",
    "\n",
    "species = uri_split[1].str.strip(')')\n",
    "species = species.str.replace('Tax', 'Taxonomy')\n",
    "species = species.fillna(value=\"Taxonomy:9606\")\n",
    "species[gnbr_df['obj_type'] != 'Gene'] = ''\n",
    "gnbr_df['obj_species'] = species"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### TODO\n",
    "There is one minor annoyance left to deal with here.  Some rows have multiple gene ids, which are stored together as a semicolon separated string.  This happens for sentences where the subject or object is a series of genes (e.g. \"MMP 8-12\").  This is a completely non-trivial to fix and only affects a couple thousand rows.  So far now will handle at the REST server, and will only put in effort for underlying fix if it turns out to be a major issue."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Fix Document IDs\n",
    "We also add the pubmed prefix to the pmids."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "pmids = gnbr_df['pmid'].astype(str)\n",
    "pmids = pmids.str.replace(r'^(\\d+)$', lambda m: 'PUBMED:' + m.group(0))\n",
    "gnbr_df['pmid'] = pmids"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>pmid</th>\n",
       "      <th>loc</th>\n",
       "      <th>subj_name</th>\n",
       "      <th>subj_loc</th>\n",
       "      <th>obj_name</th>\n",
       "      <th>obj_loc</th>\n",
       "      <th>subj_name_raw</th>\n",
       "      <th>obj_name_raw</th>\n",
       "      <th>subj_id</th>\n",
       "      <th>obj_id</th>\n",
       "      <th>...</th>\n",
       "      <th>H</th>\n",
       "      <th>H.ind</th>\n",
       "      <th>Rg</th>\n",
       "      <th>Rg.ind</th>\n",
       "      <th>Q</th>\n",
       "      <th>Q.ind</th>\n",
       "      <th>sentence_id</th>\n",
       "      <th>path_id</th>\n",
       "      <th>subj_species</th>\n",
       "      <th>obj_species</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>PUBMED:18509642</td>\n",
       "      <td>11</td>\n",
       "      <td>10058-F4</td>\n",
       "      <td>1516,1524</td>\n",
       "      <td>tumor</td>\n",
       "      <td>1492,1497</td>\n",
       "      <td>10058-F4</td>\n",
       "      <td>tumor</td>\n",
       "      <td>MESH:C524814</td>\n",
       "      <td>MESH:D009369</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>f407c175ed3c5bed6e49e853bd1521db</td>\n",
       "      <td>05d11ea4b1d924abafac60a269c3347c</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>PUBMED:20801893</td>\n",
       "      <td>10</td>\n",
       "      <td>10074-G5</td>\n",
       "      <td>1713,1721</td>\n",
       "      <td>tumor</td>\n",
       "      <td>1689,1694</td>\n",
       "      <td>10074-G5</td>\n",
       "      <td>tumor</td>\n",
       "      <td>MESH:C534883</td>\n",
       "      <td>MESH:D009369</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>432e5c439550f059249b8e05273cd1b4</td>\n",
       "      <td>05d11ea4b1d924abafac60a269c3347c</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>PUBMED:8027220</td>\n",
       "      <td>3</td>\n",
       "      <td>17-deoxysteroids</td>\n",
       "      <td>517,533</td>\n",
       "      <td>hypertension</td>\n",
       "      <td>463,475</td>\n",
       "      <td>17-deoxysteroids</td>\n",
       "      <td>hypertension</td>\n",
       "      <td>MESH:C006022</td>\n",
       "      <td>MESH:D006973</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>b7e17c4dd71970206b4bfc31b82d4eca</td>\n",
       "      <td>05d11ea4b1d924abafac60a269c3347c</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>PUBMED:18936997</td>\n",
       "      <td>15</td>\n",
       "      <td>2,3,7,8-tetrachlorodibenzo-p-dioxin</td>\n",
       "      <td>2376,2411</td>\n",
       "      <td>toxicity</td>\n",
       "      <td>2337,2345</td>\n",
       "      <td>2,3,7,8-tetrachlorodibenzo-p-dioxin</td>\n",
       "      <td>toxicity</td>\n",
       "      <td>MESH:D013749</td>\n",
       "      <td>MESH:D064420</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>71865f16aa1424cb09cac2cf609ea7cb</td>\n",
       "      <td>05d11ea4b1d924abafac60a269c3347c</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>PUBMED:26279999</td>\n",
       "      <td>0</td>\n",
       "      <td>3,5-Diiodothyronine</td>\n",
       "      <td>85,104</td>\n",
       "      <td>Cardiac_Illness</td>\n",
       "      <td>33,48</td>\n",
       "      <td>3,5-Diiodothyronine</td>\n",
       "      <td>Cardiac Illness</td>\n",
       "      <td>MESH:C030102</td>\n",
       "      <td>MESH:D006331</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>08731188178997f788b85b96e98b9120</td>\n",
       "      <td>05d11ea4b1d924abafac60a269c3347c</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 82 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              pmid  loc                            subj_name   subj_loc  \\\n",
       "0  PUBMED:18509642   11                             10058-F4  1516,1524   \n",
       "1  PUBMED:20801893   10                             10074-G5  1713,1721   \n",
       "2   PUBMED:8027220    3                     17-deoxysteroids    517,533   \n",
       "3  PUBMED:18936997   15  2,3,7,8-tetrachlorodibenzo-p-dioxin  2376,2411   \n",
       "4  PUBMED:26279999    0                  3,5-Diiodothyronine     85,104   \n",
       "\n",
       "          obj_name    obj_loc                        subj_name_raw  \\\n",
       "0            tumor  1492,1497                             10058-F4   \n",
       "1            tumor  1689,1694                             10074-G5   \n",
       "2     hypertension    463,475                     17-deoxysteroids   \n",
       "3         toxicity  2337,2345  2,3,7,8-tetrachlorodibenzo-p-dioxin   \n",
       "4  Cardiac_Illness      33,48                  3,5-Diiodothyronine   \n",
       "\n",
       "      obj_name_raw       subj_id        obj_id     ...        H H.ind  Rg  \\\n",
       "0            tumor  MESH:C524814  MESH:D009369     ...      NaN   NaN NaN   \n",
       "1            tumor  MESH:C534883  MESH:D009369     ...      NaN   NaN NaN   \n",
       "2     hypertension  MESH:C006022  MESH:D006973     ...      NaN   NaN NaN   \n",
       "3         toxicity  MESH:D013749  MESH:D064420     ...      NaN   NaN NaN   \n",
       "4  Cardiac Illness  MESH:C030102  MESH:D006331     ...      NaN   NaN NaN   \n",
       "\n",
       "  Rg.ind   Q  Q.ind                       sentence_id  \\\n",
       "0    NaN NaN    NaN  f407c175ed3c5bed6e49e853bd1521db   \n",
       "1    NaN NaN    NaN  432e5c439550f059249b8e05273cd1b4   \n",
       "2    NaN NaN    NaN  b7e17c4dd71970206b4bfc31b82d4eca   \n",
       "3    NaN NaN    NaN  71865f16aa1424cb09cac2cf609ea7cb   \n",
       "4    NaN NaN    NaN  08731188178997f788b85b96e98b9120   \n",
       "\n",
       "                            path_id  subj_species  obj_species  \n",
       "0  05d11ea4b1d924abafac60a269c3347c                             \n",
       "1  05d11ea4b1d924abafac60a269c3347c                             \n",
       "2  05d11ea4b1d924abafac60a269c3347c                             \n",
       "3  05d11ea4b1d924abafac60a269c3347c                             \n",
       "4  05d11ea4b1d924abafac60a269c3347c                             \n",
       "\n",
       "[5 rows x 82 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gnbr_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Entities and Mentions in Sentences\n",
    "Now that we've taken care of all the organization and housekeeping, it's time to start generating some node and edge files!  \n",
    "\n",
    "In this section we generate the nodes files for Entities (Chemical, Gene, Disease) and Mentions, and the edge files linking Entities to Mentions and Sentences.  Mentions nodes serve no purpose other than to optimize lookup speed for text search.  Imo, they pollute the data model, but are a necessary evil.  If I ever get around to dumping the nodes into an elastic search index, they might go away.\n",
    "\n",
    "##### Entities\n",
    "\n",
    "First order of business is pulling subject and object entities out and merging them into a single entities dataframe (concepts)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "subj_df = gnbr_df[['subj_name', 'subj_name_raw','subj_id', 'subj_species', 'subj_type', 'sentence_id']]\n",
    "subj_df.columns = ['name' , 'mention', 'uri', 'species', 'type', 'sentence_id']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "obj_df = gnbr_df[['obj_name', 'obj_name_raw','obj_id', 'obj_species', 'obj_type', 'sentence_id']]\n",
    "obj_df.columns = ['name', 'mention', 'uri', 'species', 'type', 'sentence_id']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>mention</th>\n",
       "      <th>uri</th>\n",
       "      <th>species</th>\n",
       "      <th>type</th>\n",
       "      <th>sentence_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10058-F4</td>\n",
       "      <td>10058-F4</td>\n",
       "      <td>MESH:C524814</td>\n",
       "      <td></td>\n",
       "      <td>Chemical</td>\n",
       "      <td>f407c175ed3c5bed6e49e853bd1521db</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10074-G5</td>\n",
       "      <td>10074-G5</td>\n",
       "      <td>MESH:C534883</td>\n",
       "      <td></td>\n",
       "      <td>Chemical</td>\n",
       "      <td>432e5c439550f059249b8e05273cd1b4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>17-deoxysteroids</td>\n",
       "      <td>17-deoxysteroids</td>\n",
       "      <td>MESH:C006022</td>\n",
       "      <td></td>\n",
       "      <td>Chemical</td>\n",
       "      <td>b7e17c4dd71970206b4bfc31b82d4eca</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2,3,7,8-tetrachlorodibenzo-p-dioxin</td>\n",
       "      <td>2,3,7,8-tetrachlorodibenzo-p-dioxin</td>\n",
       "      <td>MESH:D013749</td>\n",
       "      <td></td>\n",
       "      <td>Chemical</td>\n",
       "      <td>71865f16aa1424cb09cac2cf609ea7cb</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3,5-Diiodothyronine</td>\n",
       "      <td>3,5-Diiodothyronine</td>\n",
       "      <td>MESH:C030102</td>\n",
       "      <td></td>\n",
       "      <td>Chemical</td>\n",
       "      <td>08731188178997f788b85b96e98b9120</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  name                              mention  \\\n",
       "0                             10058-F4                             10058-F4   \n",
       "1                             10074-G5                             10074-G5   \n",
       "2                     17-deoxysteroids                     17-deoxysteroids   \n",
       "3  2,3,7,8-tetrachlorodibenzo-p-dioxin  2,3,7,8-tetrachlorodibenzo-p-dioxin   \n",
       "4                  3,5-Diiodothyronine                  3,5-Diiodothyronine   \n",
       "\n",
       "            uri species      type                       sentence_id  \n",
       "0  MESH:C524814          Chemical  f407c175ed3c5bed6e49e853bd1521db  \n",
       "1  MESH:C534883          Chemical  432e5c439550f059249b8e05273cd1b4  \n",
       "2  MESH:C006022          Chemical  b7e17c4dd71970206b4bfc31b82d4eca  \n",
       "3  MESH:D013749          Chemical  71865f16aa1424cb09cac2cf609ea7cb  \n",
       "4  MESH:C030102          Chemical  08731188178997f788b85b96e98b9120  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "concepts = pd.concat([subj_df, obj_df], ignore_index = True)\n",
    "concepts.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next I am finding the modt frequently used term for each entitiy, setting that as its name, and then writing to a file.  The aggregation step where I collect the mentions of each entitiy ensures that there are no duplications.  Neo4j doesn't like duplicate nodes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "from collections import Counter\n",
    "entities = concepts[['mention', 'uri', 'type', 'species']]\n",
    "entities = entities.groupby(by=['uri','type', 'species'])['mention'].apply(lambda x: x.values.tolist())\n",
    "entities = entities.apply(lambda x: Counter(x).most_common(1)[0][0])\n",
    "entities = pd.DataFrame(entities)\n",
    "entities.reset_index(inplace=True)\n",
    "entities = entities.drop_duplicates(subset=['uri'] ,keep ='last')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "entities_file = os.path.join(NEO_DIR, ENTITIES)\n",
    "entities.to_csv(entities_file, \n",
    "                columns=[\"uri\", \"type\", \"mention\", 'species'], \n",
    "                header=['uri:ID(Entity-ID)', 'type:LABEL', 'name', 'species'], \n",
    "                index=False, compression='gzip')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Mentions\n",
    "\n",
    "Now that we have entities we pull out mentions.  This is much simpler.  We dump the mentions into a dataframe, and then dedupe.  The edge file just needs to link mentions to entity uris, which I am using as unique IDs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>mention</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>26506335</th>\n",
       "      <td>rhoGDI-3</td>\n",
       "      <td>rhoGDI-3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26509670</th>\n",
       "      <td>lhx2</td>\n",
       "      <td>lhx2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26636988</th>\n",
       "      <td>retTPC/PTC</td>\n",
       "      <td>retTPC/PTC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26717150</th>\n",
       "      <td>Sting</td>\n",
       "      <td>Sting</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26734469</th>\n",
       "      <td>ubiquitin-specific_peptidase_1</td>\n",
       "      <td>ubiquitin-specific peptidase 1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    name                         mention\n",
       "26506335                        rhoGDI-3                        rhoGDI-3\n",
       "26509670                            lhx2                            lhx2\n",
       "26636988                      retTPC/PTC                      retTPC/PTC\n",
       "26717150                           Sting                           Sting\n",
       "26734469  ubiquitin-specific_peptidase_1  ubiquitin-specific peptidase 1"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mentions = concepts[['name','mention']]\n",
    "mentions = mentions.drop_duplicates()\n",
    "mentions.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "mentions_file = os.path.join(NEO_DIR, MENTIONS)\n",
    "mentions.to_csv(mentions_file, \n",
    "                columns=[\"name\", \"mention\"], \n",
    "                header=['formatted', 'mention:ID(Mention-ID)'], \n",
    "                index=False, compression='gzip')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "has_mention = concepts[['mention', 'uri']]\n",
    "has_mention = has_mention.drop_duplicates()\n",
    "has_mention_file = os.path.join(NEO_DIR, HAS_MENTION)\n",
    "has_mention.to_csv(has_mention_file, \n",
    "                columns=[\"uri\", \"mention\"], \n",
    "                header=[':START_ID(Entity-ID)', ':END_ID(Mention-ID)'], \n",
    "                index=False, compression='gzip')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### In Sentence\n",
    "Here we are making the edges that connect mentions to sentences.  This is just as simple as the mentions.  Pull out the entity ids and sentence ids, dedupe, and dump into a csv.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>mention</th>\n",
       "      <th>sentence_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10058-F4</td>\n",
       "      <td>f407c175ed3c5bed6e49e853bd1521db</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10074-G5</td>\n",
       "      <td>432e5c439550f059249b8e05273cd1b4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>17-deoxysteroids</td>\n",
       "      <td>b7e17c4dd71970206b4bfc31b82d4eca</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2,3,7,8-tetrachlorodibenzo-p-dioxin</td>\n",
       "      <td>71865f16aa1424cb09cac2cf609ea7cb</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3,5-Diiodothyronine</td>\n",
       "      <td>08731188178997f788b85b96e98b9120</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               mention                       sentence_id\n",
       "0                             10058-F4  f407c175ed3c5bed6e49e853bd1521db\n",
       "1                             10074-G5  432e5c439550f059249b8e05273cd1b4\n",
       "2                     17-deoxysteroids  b7e17c4dd71970206b4bfc31b82d4eca\n",
       "3  2,3,7,8-tetrachlorodibenzo-p-dioxin  71865f16aa1424cb09cac2cf609ea7cb\n",
       "4                  3,5-Diiodothyronine  08731188178997f788b85b96e98b9120"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "in_sentence = concepts[['mention', 'sentence_id']]\n",
    "in_sentence = in_sentence.drop_duplicates()\n",
    "in_sentence.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "in_sentence_file = os.path.join(NEO_DIR, IN_SENTENCE)\n",
    "in_sentence.to_csv(in_sentence_file, \n",
    "                   columns=[\"mention\", \"sentence_id\"], \n",
    "                   header=[\":START_ID(Mention-ID)\", \":END_ID(Sentence-ID)\"], \n",
    "                   index=False, compression='gzip')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Sentences, Themes, and Documents\n",
    "This is the core of the database in terms of the information the API provides.  The sentence annotations are where GNBR shines, so they make up the center of the data model.\n",
    "##### Sentences\n",
    "The code for generating the sentence nodes isn;t too complex.  Basically just collect take the sentences and dedupe by sentence id. Interestingly, if you dedupe using id, text, pmid triples you end up with one or two duplicate sentence ids, which crashes the neo4j import.  This behavior is caused by some sentences mapping to more than one pmid.  When I chased down the pmids, they appeared to be invalid."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "sentence_df = gnbr_df[['subj_id', 'obj_id', 'text', 'pmid', 'path', 'sentence_id', 'path_id']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sentence_id</th>\n",
       "      <th>text</th>\n",
       "      <th>pmid</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>f407c175ed3c5bed6e49e853bd1521db</td>\n",
       "      <td>Peak tumor concentrations of 10058-F4 were at ...</td>\n",
       "      <td>PUBMED:18509642</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>432e5c439550f059249b8e05273cd1b4</td>\n",
       "      <td>The lack of antitumor activity probably was ca...</td>\n",
       "      <td>PUBMED:20801893</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>b7e17c4dd71970206b4bfc31b82d4eca</td>\n",
       "      <td>A 15-yr-old patient from Germany was seen for ...</td>\n",
       "      <td>PUBMED:8027220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>71865f16aa1424cb09cac2cf609ea7cb</td>\n",
       "      <td>Dioxin-like activity of multilayer and carbon ...</td>\n",
       "      <td>PUBMED:18936997</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>08731188178997f788b85b96e98b9120</td>\n",
       "      <td>Nonthyroidal_Illness_Syndrome in Cardiac_Illne...</td>\n",
       "      <td>PUBMED:26279999</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        sentence_id  \\\n",
       "0  f407c175ed3c5bed6e49e853bd1521db   \n",
       "1  432e5c439550f059249b8e05273cd1b4   \n",
       "2  b7e17c4dd71970206b4bfc31b82d4eca   \n",
       "3  71865f16aa1424cb09cac2cf609ea7cb   \n",
       "4  08731188178997f788b85b96e98b9120   \n",
       "\n",
       "                                                text             pmid  \n",
       "0  Peak tumor concentrations of 10058-F4 were at ...  PUBMED:18509642  \n",
       "1  The lack of antitumor activity probably was ca...  PUBMED:20801893  \n",
       "2  A 15-yr-old patient from Germany was seen for ...   PUBMED:8027220  \n",
       "3  Dioxin-like activity of multilayer and carbon ...  PUBMED:18936997  \n",
       "4  Nonthyroidal_Illness_Syndrome in Cardiac_Illne...  PUBMED:26279999  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sentences = sentence_df[['sentence_id', 'text', 'pmid']]\n",
    "sentences = sentences.drop_duplicates(subset='sentence_id')\n",
    "sentences.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "sentences_file = os.path.join(NEO_DIR, SENTENCES)\n",
    "sentences.to_csv(sentences_file, \n",
    "                columns=[\"sentence_id\", \"text\", \"pmid\"], \n",
    "                header=[\":ID(Sentence-ID)\", \"text\", \"pmid\"], \n",
    "                index=False, compression='gzip')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Has Theme\n",
    "Edges between sentences and themes are also quite simple.  Just grab, sentence ids and path ids, then dedupe.  I use these edges to store the dependency paths because I don't every expect them to be the subject of a query.  Protip: model data that will be the subject of queries as nodes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sentence_id</th>\n",
       "      <th>path</th>\n",
       "      <th>path_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>f407c175ed3c5bed6e49e853bd1521db</td>\n",
       "      <td>concentrations|nmod|start_entity concentration...</td>\n",
       "      <td>05d11ea4b1d924abafac60a269c3347c</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>432e5c439550f059249b8e05273cd1b4</td>\n",
       "      <td>concentrations|nmod|start_entity concentration...</td>\n",
       "      <td>05d11ea4b1d924abafac60a269c3347c</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>b7e17c4dd71970206b4bfc31b82d4eca</td>\n",
       "      <td>concentrations|nmod|start_entity concentration...</td>\n",
       "      <td>05d11ea4b1d924abafac60a269c3347c</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>71865f16aa1424cb09cac2cf609ea7cb</td>\n",
       "      <td>concentrations|nmod|start_entity concentration...</td>\n",
       "      <td>05d11ea4b1d924abafac60a269c3347c</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>08731188178997f788b85b96e98b9120</td>\n",
       "      <td>concentrations|nmod|start_entity concentration...</td>\n",
       "      <td>05d11ea4b1d924abafac60a269c3347c</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        sentence_id  \\\n",
       "0  f407c175ed3c5bed6e49e853bd1521db   \n",
       "1  432e5c439550f059249b8e05273cd1b4   \n",
       "2  b7e17c4dd71970206b4bfc31b82d4eca   \n",
       "3  71865f16aa1424cb09cac2cf609ea7cb   \n",
       "4  08731188178997f788b85b96e98b9120   \n",
       "\n",
       "                                                path  \\\n",
       "0  concentrations|nmod|start_entity concentration...   \n",
       "1  concentrations|nmod|start_entity concentration...   \n",
       "2  concentrations|nmod|start_entity concentration...   \n",
       "3  concentrations|nmod|start_entity concentration...   \n",
       "4  concentrations|nmod|start_entity concentration...   \n",
       "\n",
       "                            path_id  \n",
       "0  05d11ea4b1d924abafac60a269c3347c  \n",
       "1  05d11ea4b1d924abafac60a269c3347c  \n",
       "2  05d11ea4b1d924abafac60a269c3347c  \n",
       "3  05d11ea4b1d924abafac60a269c3347c  \n",
       "4  05d11ea4b1d924abafac60a269c3347c  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "has_theme = sentence_df[['sentence_id', 'path', 'path_id']]\n",
    "has_theme = has_theme.drop_duplicates()\n",
    "has_theme.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "has_theme_file = os.path.join(NEO_DIR, HAS_THEME)\n",
    "has_theme.to_csv(has_theme_file, \n",
    "                 columns=[\"sentence_id\", \"path\", \"path_id\"], \n",
    "                 header=[\":START_ID(Sentence-ID)\", \"path\", \":END_ID(Path-ID)\"], \n",
    "                 index=False, compression='gzip')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Themes\n",
    "Now we start to get a little fancy and work with numeric data.  Theme distributions are indicate how strongly we believe a sentence asserts some relationship between a pair of entities. \n",
    "\n",
    "Code is simple enough:\n",
    "1. Select numeric fields in the dataframe (i.e. theme fields)  \n",
    "2. Get rid of flagship indicators\n",
    "3. Normalize theme scores\n",
    "4. Create the file header\n",
    "5. Dedupe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "themes_df = gnbr_df.select_dtypes(include='float64')\n",
    "themes_df = themes_df[[i for i in themes_df.columns if not i.endswith('.ind')]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The next line is the secret sauce.  Here we are using the pecentile rank function to normalize theme scores.  So for example we map a score for \"treats\" to its percentile rank among all the \"treats\" scores.  We do this for each theme."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "themes_df = themes_df.rank(numeric_only=True, pct=True, method='dense')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Below we do all the header creation and deduplication"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>T:float</th>\n",
       "      <th>C:float</th>\n",
       "      <th>Sa:float</th>\n",
       "      <th>Pr:float</th>\n",
       "      <th>Pa:float</th>\n",
       "      <th>J:float</th>\n",
       "      <th>Mp:float</th>\n",
       "      <th>A+:float</th>\n",
       "      <th>A-:float</th>\n",
       "      <th>B:float</th>\n",
       "      <th>...</th>\n",
       "      <th>Md:float</th>\n",
       "      <th>X:float</th>\n",
       "      <th>L:float</th>\n",
       "      <th>W:float</th>\n",
       "      <th>V+:float</th>\n",
       "      <th>I:float</th>\n",
       "      <th>H:float</th>\n",
       "      <th>Rg:float</th>\n",
       "      <th>Q:float</th>\n",
       "      <th>:ID(Path-ID)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.066845</td>\n",
       "      <td>0.177719</td>\n",
       "      <td>0.121410</td>\n",
       "      <td>0.110619</td>\n",
       "      <td>0.029617</td>\n",
       "      <td>0.161604</td>\n",
       "      <td>0.050955</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>05d11ea4b1d924abafac60a269c3347c</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141</th>\n",
       "      <td>0.000668</td>\n",
       "      <td>0.001326</td>\n",
       "      <td>0.002611</td>\n",
       "      <td>0.001475</td>\n",
       "      <td>0.003484</td>\n",
       "      <td>0.002430</td>\n",
       "      <td>0.003185</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2d7980e2baff336b50b709cbf16b52de</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>147</th>\n",
       "      <td>0.001337</td>\n",
       "      <td>0.001326</td>\n",
       "      <td>0.001305</td>\n",
       "      <td>0.005900</td>\n",
       "      <td>0.001742</td>\n",
       "      <td>0.001215</td>\n",
       "      <td>0.006369</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>a9f615f64661e4d1c328e551e6c4ff0f</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156</th>\n",
       "      <td>0.994652</td>\n",
       "      <td>0.997347</td>\n",
       "      <td>0.951697</td>\n",
       "      <td>0.989676</td>\n",
       "      <td>0.945993</td>\n",
       "      <td>0.957473</td>\n",
       "      <td>0.901274</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>ced8dc92de3e40f8cb8c8e90e5cd0d90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8521</th>\n",
       "      <td>0.000668</td>\n",
       "      <td>0.002653</td>\n",
       "      <td>0.001305</td>\n",
       "      <td>0.001475</td>\n",
       "      <td>0.001742</td>\n",
       "      <td>0.001215</td>\n",
       "      <td>0.003185</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>c95cbad08fb46a3e88d51e9222f642a8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 33 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       T:float   C:float  Sa:float  Pr:float  Pa:float   J:float  Mp:float  \\\n",
       "0     0.066845  0.177719  0.121410  0.110619  0.029617  0.161604  0.050955   \n",
       "141   0.000668  0.001326  0.002611  0.001475  0.003484  0.002430  0.003185   \n",
       "147   0.001337  0.001326  0.001305  0.005900  0.001742  0.001215  0.006369   \n",
       "156   0.994652  0.997347  0.951697  0.989676  0.945993  0.957473  0.901274   \n",
       "8521  0.000668  0.002653  0.001305  0.001475  0.001742  0.001215  0.003185   \n",
       "\n",
       "      A+:float  A-:float  B:float                ...                 Md:float  \\\n",
       "0          0.0       0.0      0.0                ...                      0.0   \n",
       "141        0.0       0.0      0.0                ...                      0.0   \n",
       "147        0.0       0.0      0.0                ...                      0.0   \n",
       "156        0.0       0.0      0.0                ...                      0.0   \n",
       "8521       0.0       0.0      0.0                ...                      0.0   \n",
       "\n",
       "      X:float  L:float  W:float  V+:float  I:float  H:float  Rg:float  \\\n",
       "0         0.0      0.0      0.0       0.0      0.0      0.0       0.0   \n",
       "141       0.0      0.0      0.0       0.0      0.0      0.0       0.0   \n",
       "147       0.0      0.0      0.0       0.0      0.0      0.0       0.0   \n",
       "156       0.0      0.0      0.0       0.0      0.0      0.0       0.0   \n",
       "8521      0.0      0.0      0.0       0.0      0.0      0.0       0.0   \n",
       "\n",
       "      Q:float                      :ID(Path-ID)  \n",
       "0         0.0  05d11ea4b1d924abafac60a269c3347c  \n",
       "141       0.0  2d7980e2baff336b50b709cbf16b52de  \n",
       "147       0.0  a9f615f64661e4d1c328e551e6c4ff0f  \n",
       "156       0.0  ced8dc92de3e40f8cb8c8e90e5cd0d90  \n",
       "8521      0.0  c95cbad08fb46a3e88d51e9222f642a8  \n",
       "\n",
       "[5 rows x 33 columns]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "themes_df = themes_df.fillna(0) \n",
    "themes_df.columns = [i + ':float' for i in themes_df.columns]\n",
    "themes_df[':ID(Path-ID)'] = gnbr_df['path_id']\n",
    "themes_df = themes_df.drop_duplicates()\n",
    "themes_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "themes_file = os.path.join(NEO_DIR, THEMES)\n",
    "themes_df.to_csv(themes_file, index=False, compression='gzip')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Initially I wanted to drop theme distributions where everything was close to zero, but this cause issues because they are referenced in an edge file (i.e. has_theme), and misaligned references crashes the neo4j import.  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Statements\n",
    "Statements are also pretty key for GNBR as they form the core of its \"reasoning\" functionality.  Statements are assertions made about relationships between pairs of entities that take into account the sum total literature evidence in pubtator.\n",
    "\n",
    "Algorithm\n",
    "1. Aggregate over each all sentences and themes connecting each entity pair using mean as aggregation function\n",
    "2. Normalize using percentile rank as described for themes.  \n",
    "3. Drop statements (rows) where all theme scores are in the bottom five percent\n",
    "4. Create file header and dedupe\n",
    "\n",
    "The next line is where all the magic (aggregation) happens."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "statements = gnbr_df.groupby(by=['subj_id','obj_id']).mean(numeric_only=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After that it's pretty much the same as the themes.  I do take the step of getting rid of statements edges where all the theme scores are close to zero.  This doesn't cause any failures.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "statements = statements.select_dtypes(include='float64')\n",
    "statements = statements.drop(['loc'], axis=1)\n",
    "statements = statements[[i for i in statements.columns if not i.endswith('.ind')]]\n",
    "statements = statements.rank(numeric_only=True, pct=True, method='dense')\n",
    "statements = statements[statements > 0.05].dropna(how='all')\n",
    "statements.columns = [i + ':float' for i in statements.columns]\n",
    "statements = pd.DataFrame(statements)\n",
    "statements.reset_index(inplace=True)\n",
    "statements = statements.fillna(0) \n",
    "statements = statements.rename(columns = {'subj_id': ':START_ID(Entity-ID)', 'obj_id': ':END_ID(Entity-ID)'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "statements_file = os.path.join(NEO_DIR, STATEMENTS)\n",
    "statements.to_csv(statements_file, index=False, compression='gzip')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Documents\n",
    "Nothing here yet.  I'm working to beef up the amount of data we offer about the publications (i.e. publication date. author, etc), but it's not so germaine to the functioning of anything right now, so it is low priority.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
